Choosing Bucket Boundaries for Histograms

Histograms have long been used to capture attribute value distribution statistics for query optimizers. More recently, there has been a growing interest in the use of histograms to produce quick approximate answers to decision support queries. This motivates nding good strategies for specifying histogram buckets. Under the assumption that nding optimal bucket boundaries is computationally ineecient, previous research has focused on nding heuristics that produce good solutions. In this paper, we present an algorithm to determine bucket boundaries optimally, in time proportional to the square of the number of distinct data values, for a broad class of optimality metrics. Through experimentation, we show that optimal histograms can have substantially lower reconstruction error than histograms produced according to popular heuristics. We also present a new heuristic, based on our understanding of the optimal solution, which in many cases obtains lower reconstruction error than previously proposed heuristics, with a computation cost that is still quite low.